Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶
Angaben Studierende(r) (fehlende Angaben ergänzen)¶
| Vorname: | Alexander |
| Nachname: | Wielengowski |
| Immatrikulationsnummer: | 24-672-380 |
| Modul: | Data Science |
| Prüfungsdatum / Raum / Zeit: | 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45 |
| Erlaubte Hilfsmittel: | w.MA.XX.DS.24HS (Data Science) Open Book, Eigener Computer, Internet-Zugang |
| Nicht erlaubt: | Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT) sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen. |
Bewertungskriterien¶
(max. erreichbare Punkte: 48)¶
| Kategorie | Beschreibung | Punkteverteilung |
|---|---|---|
| Code nicht lauffähig oder Ergebnisse nicht sinnvoll | Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. | 0 Punkte |
| Code lauffähig, aber mit gravierenden Mängeln | Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. | 25% der max. erreichbaren Punkte |
| Code lauffähig, aber mit mittleren Mängeln | Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. | 50% der max. erreichbaren Punkte |
| Code lauffähig, aber mit minimalen Mängeln | Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. | 75% der max. erreichbaren Punkte |
| Code lauffähig und korrekt | Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. | 100% der max. erreichbaren Punkte |
Python Libraries und Settings¶
In [1]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text
# Ignore warnings
import warnings
warnings.filterwarnings("ignore")
print(os.getcwd())
/workspaces/python_postgresql_postgis
Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶
1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶
GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶
Hinweis:
- Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
- Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.
2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶
In [2]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"
# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
"@" + host + ":" + port + "/" + database
# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)
# Test der Connection
with engine.connect() as connection:
result = connection.execute(text('SELECT current_database()'))
print(result.fetchone())
# Verbindung trennen
engine.dispose()
('osm_switzerland',)
Aufgaben (Dieser Teil wird bewertet!)¶
Hinweise zu den folgenden Aufgabenstellungen:
- In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
- Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
- Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
- In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
- In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
- Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
- Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [7]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """
SELECT p.osm_id, p.name, p."addr:country", st_transform(p.way, 4326) AS geom
FROM public.planet_osm_point AS p
WHERE shop='car_repair'
--AND p."addr:street" IS NOT NULL
--AND p."addr:country"='CH'
"""
### Da nicht explizit genannt, habe ich leere Strasse und Land=CH nur als Kommentar versehen.
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[7]:
| osm_id | name | addr:country | geom | |
|---|---|---|---|---|
| 0 | 1811755810 | Grenzgarage | None | POINT (9.62898 47.45412) |
| 1 | 9408250312 | Gebr. Wirth AG | None | POINT (9.63098 47.45327) |
| 2 | 2539306181 | Lantech | None | POINT (9.58444 47.46663) |
| 3 | 2530851973 | Garage Welpe | None | POINT (9.58777 47.46975) |
| 4 | 5254765356 | Richner AG | None | POINT (9.43594 47.50471) |
| ... | ... | ... | ... | ... |
| 1459 | 4171944125 | Garage Biefer | CH | POINT (9.16975 47.61128) |
| 1460 | 4171944126 | Garage plus | None | POINT (9.1698 47.60761) |
| 1461 | 4386729493 | Garage Stahel | None | POINT (9.26639 47.55844) |
| 1462 | 1724458441 | Garage Diethelm | None | POINT (9.29697 47.53936) |
| 1463 | 6092408245 | Nussberger Direktimport | None | POINT (9.34644 47.53412) |
1464 rows × 4 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [8]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[8]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
- Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [5]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """
SELECT p.osm_id, p.name, st_transform(p.way, 4326) AS geom
FROM public.planet_osm_point AS p
WHERE amenity='biergarten'
-- AND p."addr:country"='CH'
;"""
### country='CH' wurde dieses Mal bewusst ignoriert, da es sonst nur 3 Biergarten hätte. Der Datensatz beinhaltet aber sowieso
### nur die Schweiz, daher habe ich es ausnahmsweise weggelassen
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[5]:
| osm_id | name | geom | |
|---|---|---|---|
| 0 | 704467869 | Bierhalle | POINT (9.6068 47.40694) |
| 1 | 6741052485 | None | POINT (6.10754 46.16777) |
| 2 | 7554497969 | La Grange | POINT (7.15423 46.34937) |
| 3 | 454394986 | None | POINT (7.8382 46.54507) |
| 4 | 5165144561 | Osteria Sascola | POINT (8.5852 46.30886) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [6]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[6]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
- Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
- Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
- Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
- Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.
(max. erreichbare Punkte: 6)
In [11]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT p.osm_id, p."addr:street", p."addr:housenumber", p."addr:city", p."addr:postcode", p.building, ST_Transform(p.way, 4326) AS Geom
FROM public.planet_osm_polygon AS p
WHERE p."addr:street"='Stadthausstrasse'
AND p."addr:city"='Winterthur'
AND p."addr:housenumber" IS NOT NULL
AND p.building IS NOT NULL
AND p."addr:postcode" IS NOT NULL
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[11]:
| osm_id | addr:street | addr:housenumber | addr:city | addr:postcode | building | geom | |
|---|---|---|---|---|---|---|---|
| 0 | 9264543 | Stadthausstrasse | 4a | Winterthur | 8400 | government | POLYGON ((8.73111 47.50115, 8.73112 47.50085, ... |
| 1 | 22301937 | Stadthausstrasse | 4 | Winterthur | 8400 | apartments | POLYGON ((8.73232 47.50109, 8.73233 47.50107, ... |
| 2 | 75027485 | Stadthausstrasse | 31 | Winterthur | 8400 | office | POLYGON ((8.73162 47.50041, 8.73165 47.50029, ... |
| 3 | 75027472 | Stadthausstrasse | 35 | Winterthur | 8400 | apartments | POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8... |
| 4 | 75027503 | Stadthausstrasse | 37 | Winterthur | 8400 | apartments | POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8... |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 56 | 94283231 | Stadthausstrasse | 145 | Winterthur | 8400 | apartments | POLYGON ((8.72414 47.4995, 8.72417 47.49939, 8... |
| 57 | 94283304 | Stadthausstrasse | 143 | Winterthur | 8400 | office | POLYGON ((8.72427 47.49952, 8.72431 47.4994, 8... |
| 58 | 24804763 | Stadthausstrasse | 22 | Winterthur | 8400 | office | POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8... |
| 59 | 26992511 | Stadthausstrasse | 24 | Winterthur | 8400 | retail | POLYGON ((8.72405 47.49972, 8.72407 47.49967, ... |
| 60 | 134980581 | Stadthausstrasse | 10b | Winterthur | 8400 | yes | POLYGON ((8.72652 47.50075, 8.72661 47.50063, ... |
61 rows × 7 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [13]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=17,
tiles='ESRIWorldImagery')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[13]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
- Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [14]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
osm_id,
highway,
ST_Transform(p.way, 4326) AS Geom
FROM planet_osm_roads AS p
WHERE
highway='motorway'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[14]:
| osm_id | highway | geom | |
|---|---|---|---|
| 0 | 1236416666 | motorway | LINESTRING (9.64218 47.43433, 9.6422 47.43402,... |
| 1 | 552469430 | motorway | LINESTRING (9.64232 47.43412, 9.6423 47.43469) |
| 2 | 552469432 | motorway | LINESTRING (9.6423 47.43469, 9.64232 47.43504,... |
| 3 | 552469428 | motorway | LINESTRING (9.64312 47.43793, 9.6429 47.43753,... |
| 4 | 186132194 | motorway | LINESTRING (9.64235 47.43533, 9.6424 47.43568,... |
| ... | ... | ... | ... |
| 8263 | 100501651 | motorway | LINESTRING (9.15834 47.65468, 9.15789 47.65424... |
| 8264 | 277760690 | motorway | LINESTRING (9.16103 47.65931, 9.16112 47.65984... |
| 8265 | 27158313 | motorway | LINESTRING (9.16125 47.66169, 9.16124 47.66165... |
| 8266 | 318438636 | motorway | LINESTRING (9.16136 47.66118, 9.1614 47.66144) |
| 8267 | 63904479 | motorway | LINESTRING (9.1614 47.66144, 9.16145 47.66172,... |
8268 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [15]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=9,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
line_weight=3,
line_color='red'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[15]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
- Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.
(max. erreichbare Punkte: 8)
In [29]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.name,
ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
FROM
public.planet_osm_line AS p
WHERE
p.waterway='river'
GROUP BY p.osm_id, p.name
;"""
### Buffer scheint nicht zu funktionieren
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[29]:
| osm_id | name | geom | |
|---|---|---|---|
| 0 | 4223448 | Äussere Aare | POLYGON ((7.61135 46.75204, 7.61112 46.75215, ... |
| 1 | 4224616 | Innere Aare | POLYGON ((7.62089 46.74728, 7.62027 46.74748, ... |
| 2 | 4245442 | Reuss | POLYGON ((8.61599 46.8591, 8.61509 46.86111, 8... |
| 3 | 4245448 | Reuss | POLYGON ((8.62089 46.82734, 8.62063 46.82792, ... |
| 4 | 4254242 | Töss | POLYGON ((8.53617 47.55367, 8.53615 47.55396, ... |
| ... | ... | ... | ... |
| 2552 | 1317015786 | Ticino | POLYGON ((8.74907 46.50162, 8.75252 46.50114, ... |
| 2553 | 1317015787 | Ticino | POLYGON ((8.73866 46.50069, 8.73864 46.50073, ... |
| 2554 | 1318731230 | Le Rhône | POLYGON ((6.00715 46.18904, 6.00742 46.1891, 6... |
| 2555 | 1319653743 | None | POLYGON ((10.11183 46.68295, 10.11115 46.68303... |
| 2556 | 1319653744 | None | POLYGON ((10.10057 46.68987, 10.09988 46.69099... |
2557 rows × 3 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [31]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='blue'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[31]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
- Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
- Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [22]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT p.osm_id, p.name, p.shop, p."addr:city", ST_Transform(p.way, 4326) AS Geom
FROM public.planet_osm_polygon AS p
WHERE p.shop='bakery'
AND (p."addr:city"='Winterthur' OR p."addr:city"='Zürich')
ORDER BY p.osm_id
;"""
### sehr wenige Daten. Habe ich als "city" dief falsche Spalte erwischt?
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[22]:
| osm_id | name | shop | addr:city | geom | |
|---|---|---|---|---|---|
| 0 | 182237939 | Bäckerei Hug | bakery | Zürich | POLYGON ((8.51871 47.36999, 8.51873 47.36998, ... |
| 1 | 380090582 | Brezelkönig | bakery | Winterthur | POLYGON ((8.72323 47.49977, 8.72329 47.49974, ... |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [23]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=12,
tiles='ESRIWorldGrayCanvas')
# Map settings
folium.GeoJson(
gdf,
name='map',
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[23]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶
Details zur Aufgabenstellung:
- Sie finden die Daten in der Tabelle 'planet_osm_point'.
- Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
- Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [24]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.shop,
p.name,
ST_Distance(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
) AS distance_meters,
ST_TRANSFORM(p.way, 4326) AS geom
FROM
planet_osm_point AS p
WHERE
p.shop = 'hairdresser'
AND ST_DWithin(
ST_Transform(p.way, 4326)::geography,
-- Central station coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
500
)
ORDER BY distance_meters
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[24]:
| osm_id | shop | name | distance_meters | geom | |
|---|---|---|---|---|---|
| 0 | 4833061523 | hairdresser | André Joe Coiffure | 72.419054 | POINT (8.54016 47.37774) |
| 1 | 4424939218 | hairdresser | McCoiffure | 111.635316 | POINT (8.53791 47.37791) |
| 2 | 4244059289 | hairdresser | Art Coiffure Kaiser | 133.439602 | POINT (8.53788 47.37745) |
| 3 | 4424939145 | hairdresser | Saleh | 151.462092 | POINT (8.54071 47.37709) |
| 4 | 693318659 | hairdresser | Coiffeur Insieme | 168.219919 | POINT (8.53992 47.37664) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [25]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=16,
tiles='ESRIWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[25]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶
In [32]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime
print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')
----------------------------------- POSIX Linux | 6.5.0-1025-azure Datetime: 2024-10-07 10:07:44 Python Version: 3.12.1 IP Address: 127.0.0.1 -----------------------------------